﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.ComponentModel;

namespace StockService
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        static string strCon = WebConfigurationManager.ConnectionStrings["StockAppCon"].ConnectionString;

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        [WebMethod(Description = "Show stock quote of current date")]
        public DataSet GetTicker(string ticker)
        {
            SqlConnection con = new SqlConnection(strCon);
            string query = "select * from " + ticker;
            SqlDataAdapter da = new SqlDataAdapter(query, con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }

        [WebMethod(Description = "Get quote by date mm/dd/yyyy")]
        public DataSet GetTickerByDate(string ticker, string date)
        {
            SqlConnection con = new SqlConnection(strCon);
            string query = "select * from " + ticker + " where date=\'" + date + "\'";
            SqlDataAdapter da = new SqlDataAdapter(query, con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }

        [WebMethod(Description = "Get all tickers")]
        public String[] GetAllTickers()
        {
            SqlConnection con = new SqlConnection(strCon);
            string query = "select ticker from Ticker";
            SqlDataAdapter da = new SqlDataAdapter(query, con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            int count = ds.Tables[0].Rows.Count;
            string[] ticker = new string[count];
            for (int i = 0; i < count; i++)
            {
                ticker[i] = ds.Tables[0].Rows[i][0].ToString();
            }

            return ticker;
        }

        [WebMethod(Description = "Get ticker's prices in a period of time")]
        public DataSet GetTickerPrices(string ticker, string startDate, string endDate)
        {
            SqlConnection con = new SqlConnection(strCon);
            string query = "select * from " + ticker + " where date BETWEEN \'" + startDate + "\' AND \'" + endDate + "\';";
            SqlDataAdapter da = new SqlDataAdapter(query, con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }

        [WebMethod (Description="Download tickers for current date")]
        public string DownloadTickers()
        {
            // Get current date and format date in dd-mm-yyyy
            string today = DateTime.Today.ToShortDateString();
            string[] temp = today.Split('/');
            today = temp[1] + "-" + temp[0] + "-" + temp[2];
            string file = today + ".csv";

            string url = @"http://www.cophieu68.com/export/dailyexcel.php?stcid=1&date=" + today;

            // Create an instance of WebClient
            WebClient client = new WebClient();
            // Hookup DownloadFileCompleted Event
            client.DownloadFileCompleted += (sender, e) => client_DownloadFileCompleted(file, sender, e);

            // Start the download and copy the file
            client.DownloadFileAsync(new Uri(url), Server.MapPath(file));
            return "Succeeded download tickers for " + today;
        }

        [WebMethod(Description = "Download tickers by date dd-mm-yyyy")]
        public string DownloadTickersByDate(string date)
        {
            string url = @"http://www.cophieu68.com/export/dailyexcel.php?stcid=1&date=" + date;
            string file = date + ".csv";

            // Create an instance of WebClient
            WebClient client = new WebClient();
            // Hookup DownloadFileCompleted Event
            client.DownloadFileCompleted += (sender, e) => client_DownloadFileCompleted(file, sender, e);

            // Start the download and copy the file to c:\temp
            client.DownloadFileAsync(new Uri(url), Server.MapPath(file));
            return "Succeeded";
        }

        public void client_DownloadFileCompleted(string file, object sender, AsyncCompletedEventArgs e)
        {
            // Update database by downloaded file
            StreamReader sr = new StreamReader(Server.MapPath(file));
            string inputLine = "";

            String[] values = null;
            sr.ReadLine(); // skip header

            SqlConnection con = new SqlConnection(strCon);
            con.Open();

            while ((inputLine = sr.ReadLine()) != null)
            {
                values = inputLine.Split(',');
                try
                {
                    string table = "Insert" + values[0] + "Quote";
                    SqlCommand cmd = new SqlCommand(table, con);

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@ticker", values[0]);

                    cmd.Parameters.AddWithValue("@date", Convert.ToDateTime(values[1]));

                    cmd.Parameters.AddWithValue("@open_price", Convert.ToDouble(values[2]));

                    cmd.Parameters.AddWithValue("@high_price", Convert.ToDouble(values[3]));

                    cmd.Parameters.AddWithValue("@low_price", Convert.ToDouble(values[4]));

                    cmd.Parameters.AddWithValue("@close_price", Convert.ToDouble(values[5]));

                    cmd.Parameters.AddWithValue("@volume", Convert.ToInt32(values[6]));

                    int rows = cmd.ExecuteNonQuery();

                    cmd.Dispose();
                }
                catch (Exception)
                {
                    //handle exception
                }
            }
            sr.Close();


            if (con.State == ConnectionState.Open)
                con.Close();

            con.Dispose();
        }

        [WebMethod]
        public void Test()
        {
            
        }

    }
}